package dnbW.dbW.sucxentdbW;

import dnbW.entityW.Attribute;
import dnbW.entityW.Entity;
import dnbW.entityW.IEntity;
import dnbW.entityW.IPath;
import dnbW.entityW.Path;
import dnbW.entityW.Relationship;
import dnbW.utilW.Stopwatch;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

/**
 * This class is in charge of identifying and managing all entity types in a data source
 * @author truongbaquan
 */
public class EntityManager {
    private SucxentDataSource dataSource;

    // <editor-fold defaultstate="collapsed" desc="Entities">
    private Map<String, Entity> entityMap;

    public Entity getEntity(String entityName) {
        return this.entityMap.get(entityName);
    }

    public Collection<Entity> getEntities() {
        return this.entityMap.values();
    }
    // </editor-fold>

    private Map<String, Attribute> attributeMap;
    private Collection<IdPath> idPaths;

    public EntityManager(SucxentDataSource dataSource) {
        this.dataSource = dataSource;
        loadEntities();
        loadRelationships();
        buildERMap();
    }

    private void loadEntities() {
        try {
            Set<IPath> entityPathSet;
            if (SucxentDatabaseEngine.isTableExisting(dataSource.getDatabase(), dataSource.getName() + "_Entity")) {
                entityPathSet = this.loadEntityPaths();
            }
            else {
                entityPathSet = new HashSet<IPath>();
                entityPathSet.addAll(this.dataSource.getCardinalityManager().getInternalPaths());
                this.createEntityTable();
                this.insertIntoEntityTable(entityPathSet);
            }
            this.buildEntityPathMap(entityPathSet);

            this.attributeMap = new TreeMap<String, Attribute>();
            LeafPath[] leafPaths = this.dataSource.getLeafPathManager().getPaths();
            for (LeafPath leafPath : leafPaths) {
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.start();
                // find the attribute's entity
                IEntity entity = this.findEntityFromPath(leafPath);
                // create a new attribute on that entity
                Attribute attribute = new Attribute(leafPath, entity);
                // compute the frequency of the attribute in its entity
                this.computeAttribFreq(attribute);
                // load sample values for the attribute
                this.loadSampleValues(attribute);
                // store the attribute into the entity (automatically sorted)
                entity.addAttribute(attribute);
                // store the attribute with its SQL path exp into the map for easy reference
                this.attributeMap.put(leafPath.toSQLPathExp(), attribute);
                stopwatch.stop();
                System.out.println("Time to process leaf path " + leafPath.toPathExp() + " = " + stopwatch.readTime());
            }
        }
        catch (SQLException ex) {
            System.err.println(ex.getMessage());
            ex.printStackTrace();
        }
    }

    private Map<String, Entity> buildEntityPathMap(Collection<IPath> entityPaths) {
        this.entityMap = new TreeMap<String, Entity>();

        for (IPath path : entityPaths) {
            Entity entity = new Entity(path);
            this.entityMap.put(path.toSQLPathExp(), entity);
        }
        return this.entityMap;
    }
    
    private Set<IPath> loadEntityPaths() throws SQLException {
        Set<IPath> paths = new HashSet<IPath>();
        
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

        String sql = "select PathExp from " + this.dataSource.getName() + "_Entity";
        ResultSet sqlResult = statement.executeQuery(sql);
        while (sqlResult.next()) {
            paths.add(Path.buildPathFromSqlPathExp(dataSource, sqlResult.getString("PathExp")));
        }
        return paths;
    }
    
    private void createEntityTable() throws SQLException {
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");
        String sql = "create table " + this.dataSource.getName() + "_Entity (" +
                "EntityId int NOT NULL, " +
                "PathExp varchar(400) NOT NULL)";
        statement.executeUpdate(sql);
        statement.close();
    }

    private void insertIntoEntityTable(Set<IPath> entityPaths) throws SQLException {
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");
        statement.executeUpdate("delete from " + this.dataSource.getName() + "_Entity");
        String template = "INSERT INTO " + this.dataSource.getName() + "_Entity(EntityId, PathExp)" +
                "VALUES (#EntityId#, '#PathExp#')";
        int id = 1;
        for (IPath path : entityPaths) {
            String sql = template.replace("#EntityId#", String.valueOf(id)).replace("#PathExp#", path.toSQLPathExp());
            statement.executeUpdate(sql);
        }
    }

    public void showDialog() {
//        EntitySelectionDialog dialog =
//                new EntitySelectionDialog(DNB.getApplication().getMainFrame(), true,
//                dataSource.getLeafPathManager(),
//                entityMap.keySet(), idPaths);
//        dialog.setVisible(true);
//
//        if (dialog.getReturnStatus() == EntitySelectionDialog.RET_OK) {
//            Set<IPath> entityPaths = dialog.getResultEntityPaths(this.dataSource);
//            this.entityMap = this.buildEntityPathMap(entityPaths);
//            this.idPaths = dialog.getResultRelationships();
//            try {
//                for (IdPath idPath : this.idPaths) {
//                    for (IdRelation relation : idPath.getRelations()) {
//                        dataSource.getKeyManager().setCardinality(relation);
//                    }
//                }
//                this.dataSource.getKeyManager().insertIntoDatabase(idPaths);
//                this.insertIntoEntityTable(entityPaths);
//            }
//            catch (SQLException ex) {
//                System.err.println(ex.getMessage());
//                ex.printStackTrace(System.err);
//            }
//            this.buildERMap();
//        }
    }

    private void loadRelationships() {
        idPaths = this.dataSource.getKeyManager().findIdRelationships();
    }

    private void buildERMap() {
        for (IdPath idPath : idPaths) {
            IEntity entity = this.findEntityFromPath(idPath);
            if (entity != null) {
                for (IdRelation relation : idPath.getRelations()) {
                    IEntity otherEntity = this.findEntityFromPath(relation.getIdRef());
                    if (otherEntity != null && !otherEntity.equals(entity) && entity.getRelationship(otherEntity) == null) {
                        new Relationship(entity, relation.getIdCardinality(), otherEntity, relation.getIdRefCardinality());
                    }
                }
            }
        }

//        System.out.println("Entities : ");
//        for (Entity entity : this.entityMap.values()) {
//            System.out.println("\t" + entity);
//            for (Entity otherEntity : entity.getRelatedEntities()) {
//                System.out.println("\t\t" + otherEntity);
//            }
//        }
//        System.out.println();
    }

    public IEntity findEntityFromPath(IPath path) {
        String sqlPathExp = path.toSQLPathExp();
        Attribute attribute = this.attributeMap.get(sqlPathExp);
        if (attribute != null) return attribute.getEntity();
        else {
            while (sqlPathExp.length() > 0) {
                if (this.entityMap.containsKey(sqlPathExp)) {
                    return entityMap.get(sqlPathExp);
                }
                int index = sqlPathExp.lastIndexOf('.');
                sqlPathExp = sqlPathExp.substring(0, index);
            }
            return null;
        }
    }

    public int computeAttribFreq(Attribute attribute) throws SQLException {
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

        String sql = "select COUNT(*) from\n" +
                    "(select distinct Leaf.DocId, Leaf.AncestorDeweyGroup / Level.AncestorValue as Ancestor\n" +
                    "from #DataSrc#_#IsAttribute# Leaf join #DataSrc#_DocumentRValue Level on Leaf.DocId = Level.DocId\n" +
                    "where Leaf.PathId = #PathId# and Level.Level = #EntityLevel#) as T\n";
        sql = sql.replaceAll("#DataSrc#", this.dataSource.getName());
        if (attribute.getPath().isAttribute()) {
            sql = sql.replace("#IsAttribute#", "Attribute");
        }
        else {
            sql = sql.replace("#IsAttribute#", "PathValue");
        }
        sql = sql.replace("#PathId#", ((ILeafPath)attribute.getPath()).getPathId() + "");
        sql = sql.replace("#EntityLevel#", (attribute.getEntity().getPath().getLevel() - 1) + "");
        ResultSet sqlResult = statement.executeQuery(sql);
        
        int freq = 0;
        if (sqlResult.next()) {
             freq = sqlResult.getInt(1);
        }
        attribute.setFrequency(freq);
        return freq;
    }

    public void loadSampleValues(Attribute attribute) throws SQLException {
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

        String sql = "select TOP #Num# LeafValue, COUNT(*) as Freq from #DataSrc#_#IsAttribute# where PathId = #PathId#\n" +
                        "group by LeafValue order by Freq desc";
        sql = sql.replace("#Num#", Attribute.NUMBER_OF_SAMPLE_VALUES + "");
        sql = sql.replaceAll("#DataSrc#", this.dataSource.getName());
        if (attribute.getPath().isAttribute()) {
            sql = sql.replace("#IsAttribute#", "Attribute");
        }
        else {
            sql = sql.replace("#IsAttribute#", "PathValue");
        }
        sql = sql.replace("#PathId#", ((ILeafPath)attribute.getPath()).getPathId() + "");
        ResultSet sqlResult = statement.executeQuery(sql);
        while (sqlResult.next()) {
            String value = sqlResult.getString("LeafValue");
            int freq = sqlResult.getInt("Freq");
            attribute.addSampleValue(value, freq);
        }
    }
}
